* ---------------------------------------------------------------------------------------------------
* import_health: Imports raw holdings data for U.S. health insurers
*   - This is an array job: it needs to be run separately for each quarter
* ---------------------------------------------------------------------------------------------------

* Get date
local base_date = tq(2005q1)
local date_q = `base_date' + `1' - 1
local year = year(dofq(`date_q'))
local quarter = quarter(dofq(`date_q'))
local _date_q = "`year'Q`quarter'"
local _date_q_lower = "`year'q`quarter'"
di ""
di "Now processing: `_date_q'"

* Get insurers list
use $temp/insurance/lists/health_insurers_list_`year', clear

* Confirm files are all present
local N = _N
di "Total insurers: `N'"
local error_count = 0
forvalues i=1/`N' {
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)
    local fpath = ""
    cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
    if _rc==0 {
        local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
    }
    else {
        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        if _rc == 0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }
        else {
            local error_count = `error_count' + 1
        }
    }
}
if `error_count' == 0 {
    di "SUCCESS: All health insurers present for `_date_q'!"
}
else {
    di "WARNING: Missing `error_count' insurers for `_date_q'"
}



* Process bonds
local imported_count = 0
forvalues i=1/`N' {
    
    * Get file path
    use $temp/insurance/lists/health_insurers_list_`year', clear
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)

    * Run the import if available
    cap {

        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        if _rc==0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        }
        else {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }
        
        local insurer = subinstr("`fpath'", "-`_date_q'.xls", "", .)
        local insurer = subinstr("`insurer'", "$raw/sp_insurance/health/", "", .)
            
        import excel using "`fpath'", clear sheet("Bonds")
        drop if _n < 7
        drop if _n >= 2 & _n <= 7
        foreach var of varlist * {
        cap {
             local try = strtoname(`var'[1]) 
             rename `var'  `try' 
        }
        }

        cap drop AD
        cap drop AE

        keep Entity_Name CUSIP__ Group_Name Investment_Current_ Issuer_Name__ Asset_Issuer_Type__ ///
            Asset_Description_ Asset_Type Carrying_Value____000_ Conditional_Fair_Value____000_ Actual_Cost____000_ ///
            Dividend_Rate_____ Shares_Owned___actual_ Aggregate_Par_Value____000_ Change_in_Unrealized_Value____00 ///
            Fair_Value_per_Unit_____ Maturity_Date NAIC_Designation_or_Market_Indic _Moody_Credit_Rating__ ///
            _Credit_Rating_Direction__ Original_Investment_Date Latest_Investment_Date Bond_Characteristic ///
            As_Reported_Asset_Type__ As_Reported_Issuer_Type_ MI_Schedule_Name
        drop if _n == 1

        rename (Entity_Name CUSIP__ Group_Name Investment_Current_ Issuer_Name__ Asset_Issuer_Type__ Asset_Description_ Asset_Type Carrying_Value____000_ Conditional_Fair_Value____000_ Actual_Cost____000_ Dividend_Rate_____ Shares_Owned___actual_ Aggregate_Par_Value____000_ Change_in_Unrealized_Value____00 Fair_Value_per_Unit_____ Maturity_Date NAIC_Designation_or_Market_Indic _Moody_Credit_Rating__ _Credit_Rating_Direction__ Original_Investment_Date Latest_Investment_Date Bond_Characteristic As_Reported_Asset_Type__ As_Reported_Issuer_Type_ MI_Schedule_Name) (Entity_Name CUSIP Group_Name Investment_Current Issuer_Name Asset_Issuer_Type Asset_Description Asset_Type Carrying_Value Conditional_Fair_Value Actual_Cost Dividend_Rate Shares_Owned Aggregate_Par_Value Change_in_Unrealized_Value Fair_Value_per_Unit Maturity_Date NAIC_Designation Moody_Credit_Rating Credit_Rating_Direction Original_Investment_Date Latest_Investment_Date Bond_Characteristic As_Reported_Asset_Type As_Reported_Issuer_Type MI_Schedule_Name)

        destring Carrying_Value, force replace
        destring Conditional_Fair_Value, force replace
        destring Actual_Cost, force replace
        destring Dividend_Rate, force replace
        destring Shares_Owned, force replace
        destring Aggregate_Par_Value, force replace
        destring Change_in_Unrealized_Value, force replace
        destring Fair_Value_per_Unit, force replace

        gen date_q = tq(`_date_q_lower')
        gen marketvalue_usd = Conditional_Fair_Value * 1000
        gen company_id = "`insurer'"
        gen asset_class = "Bonds"
        gen insurer_type = "Health"

        save "$temp/insurance/health/bonds/`insurer'_`_date_q_lower'.dta", replace    
        local imported_count = `imported_count' + 1

    }
    
    if mod(`imported_count', 10) == 0 {
        di "Imported bonds data for `imported_count' of `N' insurers"
    }
}

di "\nCOMPLETED - Imported bonds data for a total of `imported_count' health insurers, `_date_q'"







* Process common equities
local imported_count = 0
forvalues i=1/`N' {
    
    * Get file path
    use $temp/insurance/lists/health_insurers_list_`year', clear
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)

    cap {

        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        if _rc==0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        }
        else {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }
        
        local insurer = subinstr("`fpath'", "-`_date_q'.xls", "", .)
        local insurer = subinstr("`insurer'", "$raw/sp_insurance/health/", "", .)
            
        import excel using "`fpath'", clear sheet("Com_Stk")
        drop if _n < 7
        drop if _n >= 2 & _n <= 7
        foreach var of varlist * {
        cap {
             local try = strtoname(`var'[1]) 
             rename `var'  `try' 
        }
        }

        cap drop AD
        cap drop AE

        keep Entity_Name    CUSIP__ Group_Name  Affiliated_Investment_ ///
            Investment_Current_ Issuer_Name__   Asset_Issuer_Type__ Asset_Description_  Asset_Type ///
            Shares_Owned___actual_  Carrying_Value____000_  Conditional_Fair_Value____000_ ///
            Actual_Cost____000_ Fair_Value_per_Unit_____    Interest_or_Dividends_Received__ ///
            Dividends_Declared_but_Unpaid___    Change_in_Unrealized_Value____00    Impairment____000_ ///
            NAIC_Designation_or_Market_Indic    Original_Investment_Date    Latest_Investment_Date ///
            As_Reported_Asset_Type__    As_Reported_Issuer_Type_    MI_Schedule_Name

        drop if _n == 1

        rename (Entity_Name CUSIP__ Group_Name  Affiliated_Investment_  Investment_Current_ Issuer_Name__   Asset_Issuer_Type__ Asset_Description_  Asset_Type  Shares_Owned___actual_  Carrying_Value____000_  Conditional_Fair_Value____000_  Actual_Cost____000_ Fair_Value_per_Unit_____    Interest_or_Dividends_Received__    Dividends_Declared_but_Unpaid___    Change_in_Unrealized_Value____00    Impairment____000_  NAIC_Designation_or_Market_Indic    Original_Investment_Date    Latest_Investment_Date  As_Reported_Asset_Type__    As_Reported_Issuer_Type_    MI_Schedule_Name) (Entity_Name CUSIP Group_Name Affiliated_Investment Investment_Current Issuer_Name Asset_Issuer_Type Asset_Description Asset_Type Shares_Owned Carrying_Value Conditional_Fair_Value Actual_Cost Fair_Value_per_Unit Interest_or_Dividends_Receive Dividends_Declared_but_Unpaid Change_in_Unrealized_Value Impairment NAIC_Designation Original_Investment_Date Latest_Investment_Date As_Reported_Asset_Type As_Reported_Issuer_Type MI_Schedule_Name)

        destring Shares_Owned, force replace
        destring Carrying_Value, force replace
        destring Conditional_Fair_Value, force replace
        destring Actual_Cost, force replace
        destring Fair_Value_per_Unit, force replace
        destring Interest_or_Dividends_Receive, force replace
        destring Dividends_Declared_but_Unpaid, force replace
        destring Change_in_Unrealized_Value, force replace
        destring Impairment, force replace

        gen date_q = tq(`_date_q_lower')
        gen marketvalue_usd = Conditional_Fair_Value * 1000
        gen company_id = "`insurer'"
        gen asset_class = "Common Equities"
        gen insurer_type = "Health"

        save "$temp/insurance/health/common_equities/`insurer'_`_date_q_lower'.dta", replace
        local imported_count = `imported_count' + 1

    }
    
    if mod(`imported_count', 10) == 0 {
        di "Imported common equities data for `imported_count' of `N' insurers"
    }
}

di "\nCOMPLETED - Imported common equities data for a total of `imported_count' health insurers, `_date_q'"








* Process preferred equities
local imported_count = 0
forvalues i=1/`N' {
    
    * Get file path
    use $temp/insurance/lists/health_insurers_list_`year', clear
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)

    cap {

        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        if _rc==0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        }
        else {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }
        
        local insurer = subinstr("`fpath'", "-`_date_q'.xls", "", .)
        local insurer = subinstr("`insurer'", "$raw/sp_insurance/health/", "", .)
            
        import excel using "`fpath'", clear sheet("Pref_Stk")
        drop if _n < 7
        drop if _n >= 2 & _n <= 7
        foreach var of varlist * {
        cap {
             local try = strtoname(`var'[1]) 
             rename `var'  `try' 
        }
        }

        cap drop AD
        cap drop AE
        cap drop AA
        cap drop AB

        keep Entity_Name    CUSIP__ Group_Name  Affiliated_Investment___    Investment_Current_ Issuer_Name__   Asset_Issuer_Type__ Asset_Description_  Asset_Type  Shares_Owned___actual_  Carrying_Value____000_  Conditional_Fair_Value____000_  Fair_Value_per_Unit_____    Actual_Cost____000_ Interest_or_Dividends_Received__    Dividends_Declared_but_Unpaid___    Change_in_Unrealized_Value____00    Accretion____000_   Chg_in_Carrying_Value____000_   _Moody_Credit_Rating__  As_Reported_Asset_Type__    As_Reported_Issuer_Type_    MI_Schedule_Name

        drop if _n == 1

        rename (Entity_Name CUSIP__ Group_Name Affiliated_Investment___ Investment_Current_ Issuer_Name__ Asset_Issuer_Type__ Asset_Description_ Asset_Type Shares_Owned___actual_ Carrying_Value____000_ Conditional_Fair_Value____000_ Fair_Value_per_Unit_____ Actual_Cost____000_ Interest_or_Dividends_Received__ Dividends_Declared_but_Unpaid___ Change_in_Unrealized_Value____00 Accretion____000_ Chg_in_Carrying_Value____000_ _Moody_Credit_Rating__ As_Reported_Asset_Type__ As_Reported_Issuer_Type_ MI_Schedule_Name) (Entity_Name CUSIP Group_Name Affiliated_Investment Investment_Current Issuer_Name Asset_Issuer_Type Asset_Description Asset_Type Shares_Owned Carrying_Value Conditional_Fair_Value Fair_Value_per_Unit Actual_Cost Interest_or_Dividends_Received Dividends_Declared_but_Unpaid Change_in_Unrealized_Value Accretion Chg_in_Carrying_Value Moody_Credit_Rating As_Reported_Asset_Type As_Reported_Issuer_Type MI_Schedule_Name)

        destring Shares_Owned, force replace
        destring Carrying_Value, force replace
        destring Conditional_Fair_Value, force replace
        destring Fair_Value_per_Unit, force replace
        destring Actual_Cost, force replace
        destring Interest_or_Dividends_Received, force replace
        destring Dividends_Declared_but_Unpaid, force replace
        destring Change_in_Unrealized_Value, force replace
        destring Accretion, force replace
        destring Chg_in_Carrying_Value, force replace

        gen date_q = tq(`_date_q_lower')
        gen marketvalue_usd = Conditional_Fair_Value * 1000
        gen company_id = "`insurer'"
        gen asset_class = "Preferred Equities"
        gen insurer_type = "Health"

        save "$temp/insurance/health/preferred_equities/`insurer'_`_date_q_lower'.dta", replace
        local imported_count = `imported_count' + 1

    }
    
    if mod(`imported_count', 10) == 0 {
        di "Imported preferred equities data for `imported_count' of `N' insurers"
    }
}

di "\nCOMPLETED - Imported preferred equities data for a total of `imported_count' health insurers, `_date_q'"











* Process other assets
local imported_count = 0
forvalues i=1/`N' {
    
    * Get file path
    use $temp/insurance/lists/health_insurers_list_`year', clear
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)

    cap {

        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        if _rc==0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        }
        else {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }
        
        local insurer = subinstr("`fpath'", "-`_date_q'.xls", "", .)
        local insurer = subinstr("`insurer'", "$raw/sp_insurance/health/", "", .)
            
        import excel using "`fpath'", clear sheet("Oth_Inv_Asts")
        drop if _n < 7
        drop if _n >= 2 & _n <= 7
        foreach var of varlist * {
        cap {
             local try = strtoname(`var'[1]) 
             rename `var'  `try' 
        }
        }

        cap drop AD
        cap drop AE
        cap drop AA
        cap drop AB
        cap drop X

        keep Entity_Name CUSIP__ Group_Name Asset_Type Affiliated_Investment__ Investment_Current_ City__ State_or_Other_Location__ Vendor_or_General_Partner__ Original_Investment_Date Asset_Type_and_Investment_Strate Actual_Cost____000_ Conditional_Fair_Value____000_ Carrying_Value____000_ Change_in_Unrealized_Value____00 Impairment____000_ Capitalized_Deferred_Interest___ Investment_Income____000_ Commitment_for_Additional_Invest Shares_Owned__Shares_Outstanding MI_Schedule_Name

        drop if _n == 1

        rename Entity_Name Entity_Name
        rename CUSIP__ CUSIP
        rename Group_Name Group_Name
        rename Asset_Type Asset_Type
        rename Affiliated_Investment__ Affiliated_Investment
        rename Investment_Current_ Investment_Current
        rename City__ Oth_City
        rename State_or_Other_Location__ Oth_State_or_Other_Location
        rename Vendor_or_General_Partner__ Oth_Vendor_or_General_Partner
        rename Original_Investment_Date Original_Investment_Date
        rename Asset_Type_and_Investment_Strate Asset_Type_and_Investment_Strate
        rename Actual_Cost____000_ Actual_Cost
        rename Conditional_Fair_Value____000_ Conditional_Fair_Value
        rename Carrying_Value____000_ Carrying_Value
        rename Change_in_Unrealized_Value____00 Change_in_Unrealized_Value
        rename Impairment____000_ Impairment
        rename Capitalized_Deferred_Interest___ Capitalized_Deferred_Interest
        rename Investment_Income____000_ Investment_Income
        rename Commitment_for_Additional_Invest Commitment_for_Additional_Invest
        rename Shares_Owned__Shares_Outstanding Shares_Owned_by_Outstanding
        rename MI_Schedule_Name MI_Schedule_Name

        destring Actual_Cost, force replace
        destring Conditional_Fair_Value, force replace
        destring Carrying_Value, force replace
        destring Change_in_Unrealized_Value, force replace
        destring Impairment, force replace
        destring Capitalized_Deferred_Interest, force replace
        destring Investment_Income, force replace
        destring Commitment_for_Additional_Invest, force replace
        destring Shares_Owned_by_Outstanding, force replace

        gen date_q = tq(`_date_q_lower')
        gen marketvalue_usd = Conditional_Fair_Value * 1000
        gen company_id = "`insurer'"
        gen asset_class = "Other"
        gen insurer_type = "Health"

        save "$temp/insurance/health/other/`insurer'_`_date_q_lower'.dta", replace
        local imported_count = `imported_count' + 1

    }
    
    if mod(`imported_count', 10) == 0 {
        di "Imported other assets data for `imported_count' of `N' insurers"
    }
}

di "\nCOMPLETED - Imported other assets data for a total of `imported_count' health insurers, `_date_q'"







* Process mortgage loans
local imported_count = 0
forvalues i=1/`N' {
        
    * Get file path
    use $temp/insurance/lists/health_insurers_list_`year', clear
    local firm = subinstr(firm[`i'], " ", "_", .)
    local firm = subinstr("`firm'", "/", "-", .)

    cap {

        cap confirm file "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        if _rc==0 {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xlsm"
        }
        else {
            local fpath = "$raw/sp_insurance/health/`firm'-`_date_q'.xls"
        }

        local insurer = subinstr("`fpath'", "-`_date_q'.xls", "", .)
        local insurer = subinstr("`insurer'", "$raw/sp_insurance/health/", "", .)

        import excel using "`fpath'", clear sheet("MRTG_Loans")
        drop if _n < 7
        drop if _n >= 2 & _n <= 7
        foreach var of varlist * {
        cap {
             local try = strtoname(`var'[1]) 
             rename `var'  `try' 
        }
        }

        cap drop V
        drop if _n == 1

        rename (Mortgage_Loan_Number_   Asset_Type  Investment_Current_  City_  State_or_Other_Location_  Mortgage_Loan_Standing_  Original_Investment_Date  Mortgage_Loan_Building___Land_Va  Effective_Interest_Rate____  Change_in_Unrealized_Value____00  Accretion____000_  Impairment____000_  Capitalized_Deferred_Interest___  Foreign_Exchange_Change_in_Book_  Mortgage_Loan_Book_Value_excl_Ac  Appraisal_Date__) (Mortgage_Loan_Number Asset_Type Investment_Current  City State_or_Other_Location Mortgage_Loan_Standing Original_Investment Building_and_Land_Value Effective_Interest_Rate Change_in_Unrealized_Value Accretion Impairment Capitalized_Deferred_Interest Foreign_Exchange_Change_in_Book Book_Value_ex_Accrued_Interest Appraisal_Date)

        destring Mortgage_Loan_Standing, force replace
        destring Effective_Interest_Rate, force replace
        destring Change_in_Unrealized_Value, force replace
        destring Accretion, force replace
        destring Impairment, force replace
        destring Capitalized_Deferred_Interest, force replace
        destring Foreign_Exchange_Change_in_Book, force replace
        destring Book_Value_ex_Accrued_Interest, force replace
        destring Building_and_Land_Value, force replace

        gen date_q = tq(`_date_q_lower')
        gen building_land_value_usd = Building_and_Land_Value * 1000
        gen book_value_usd = Book_Value_ex_Accrued_Interest * 1000

        gen company_id = "`insurer'"
        gen asset_class = "Mortgage Loans"
        gen insurer_type = "Health"

        save "$temp/insurance/health/loans/`insurer'_`_date_q_lower'.dta", replace
        local imported_count = `imported_count' + 1

    }

    
    if mod(`imported_count', 10) == 0 {
        di "Imported loans assets data for `imported_count' of `N' insurers"
    }
}

di "\nCOMPLETED - Imported loans assets data for a total of `imported_count' health insurers, `_date_q'"
